/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DBman;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 *
 * @author user
 */
public class DB_Admin {

    private static Connection _Connection;
    private static String _FileName
            = "C:\\Users\\Administrator\\Documents\\lombozProjects\\WTP-Vikings-APS\\src\\DBman\\database2.properties";
    
    public DB_Admin() throws IOException, ClassNotFoundException, SQLException
    {
        this.init();
    }
    
    private void init()throws IOException, ClassNotFoundException, SQLException
    {
        Properties props = new Properties();
        FileInputStream in = new FileInputStream(_FileName);
        props.load(in);
        
        String driver = props.getProperty("jdbc.driver");
        String url = props.getProperty("jdbc.url");
        String username = props.getProperty("jdbc.username");
        String password = props.getProperty("jdbc.password");
        Class.forName(driver);
        
        _Connection = DriverManager.getConnection(url,username,password);
    }
    
    public static void createTables() throws SQLException
    {  
        Statement stat = _Connection.createStatement();
        
        stat.execute("CREATE TABLE Test (Name VARCHAR(20))");
        
        stat.execute("CREATE TABLE Users (UserID VARCHAR(50) PRIMARY KEY, Password VARCHAR(50), Name VARCHAR(100))");
        
        stat.execute("CREATE TABLE BillingSiteTypes (Type VARCHAR(50))");
        stat.execute("CREATE TABLE ErrorCodes (ErrorCode VARCHAR(50), Description VARCHAR(200))");
        
        stat.execute("CREATE TABLE BillingSites (BillingSiteRef VARCHAR(50) PRIMARY KEY," +
                " Type VARCHAR(50), Script VARCHAR(100), MaintanenceWindow VARCHAR(100), RetryInterval INTEGER," +
                " BillingCycle INTEGER, LeadTime INTEGER, Concurrent INTEGER," +
                " Ident0 VARCHAR(100),Ident1 VARCHAR(100),Ident2 VARCHAR(100),Ident3 VARCHAR(100),Ident4 VARCHAR(100)," +
                " Auth0 VARCHAR(100), Auth1 VARCHAR(100), Auth2 VARCHAR(100), Auth3 VARCHAR(100), Auth4 VARCHAR(100))");
        
        stat.execute("CREATE TABLE Jobs (UserID VARCHAR(50), BillingSiteRef VARCHAR(50)," +
                " LastRun DATE, LastStatementDate DATE, LastStatementTotal DECIMAL(10,2), LatestResult VARCHAR(50)," +
                " Status VARCHAR(10)," +
                " IdentVal0 VARCHAR(100),IdentVal1 VARCHAR(100),IdentVal2 VARCHAR(100),IdentVal3 VARCHAR(100),IdentVal4 VARCHAR(100)," +
                " AuthVal0 VARCHAR(100), AuthVal1 VARCHAR(100), AuthVal2 VARCHAR(100), AuthVal3 VARCHAR(100), AuthVal4 VARCHAR(100)," +
                " PRIMARY KEY (UserID, BillingSiteRef))");
        
        stat.execute("CREATE TABLE Common (UserID VARCHAR(50), BillingSiteRef VARCHAR(50)," +
                " AccNum VARCHAR(100), AccHolder VARCHAR(100), StateDate DATE, StateNum VARCHAR(100)," +
                " StateMonth VARCHAR(100), TotalDue DECIMAL(10,2), DueDate DATE, " +
                " OpenBal DECIMAL(10,2), CloseBal DECIMAL(10,2), PayReceived DECIMAL(10,2)," +
                " NewCharges DECIMAL(10,2), Deducts DECIMAL(10,2), Discount DECIMAL(10,2), Vat DECIMAL(10,2)," +
                " PRIMARY KEY (UserID, BillingSiteRef))");
        
        stat.execute("CREATE TABLE CreditCards (UserID VARCHAR(50), BillingSiteRef VARCHAR(50)," +
                " CardType VARCHAR(100), IntRate VARCHAR(100)," +
                " CredLimit DECIMAL(10,2), CredAvail DECIMAL(10,2), MinDue DECIMAL(10,2)," +
                " PRIMARY KEY (UserID, BillingSiteRef))");
        
        stat.execute("CREATE TABLE Telco (UserID VARCHAR(50), BillingSiteRef VARCHAR(50)," +
                " TelNum VARCHAR(100), TotalDuration VARCHAR(100)," +
                " ServiceChrg DECIMAL(10,2), CallChrg DECIMAL(10,2), TotalCalls INTEGER," +
                " PRIMARY KEY (UserID, BillingSiteRef))");
        
        stat.execute("CREATE TABLE Municipality (UserID VARCHAR(50), BillingSiteRef VARCHAR(50)," +
                " ElecUsed VARCHAR(100), ElecCharge DECIMAL(10,2)," +
                " WaterUsed VARCHAR(100), WaterCharge DECIMAL(10,2)," +
                " SewageCharge DECIMAL(10,2), RefuseCharge DECIMAL(10,2)," +
                " PRIMARY KEY (UserID, BillingSiteRef))");

    }
    
    public static void populateTables() throws SQLException
    {
        Statement stat = _Connection.createStatement();
        
        stat.execute("INSERT INTO Test VALUES ('Romeo')");
        
        //Users
        stat.execute("INSERT INTO Users VALUES ('UID001','hat','Peter Parker')");
        stat.execute("INSERT INTO Users VALUES ('UID002','hat','David Donald')");
        stat.execute("INSERT INTO Users VALUES ('UID003','hat','Fred Frank')");
        
        //Types
        stat.execute("INSERT INTO BillingSiteTypes VALUES ('telco')");
        stat.execute("INSERT INTO BillingSiteTypes VALUES ('municipality')");
        stat.execute("INSERT INTO BillingSiteTypes VALUES ('credit')");
        
        //BillingSites
        stat.execute("INSERT INTO BillingSites VALUES " +
                "('BR001','telco','script.xml','one day',3,30,5,6," +
                "'User Name','','','',''," +
                "'Password','','','','')");
        stat.execute("INSERT INTO BillingSites VALUES " +
                "('BR002','municipality','script2.xml','2 day',7,60,6,5," +
                "'User Name','Address','','',''," +
                "'Password','','','','')");
        stat.execute("INSERT INTO BillingSites VALUES " +
                "('BR003','credit','script3.xml','3 day',3,30,5,6," +
                "'User Name','Card Number','','',''," +
                "'Password','PIN','','','')");
         
        
        //Jobs
        stat.execute("INSERT INTO Jobs VALUES ('UID001','BR001','06/09/2010','05/30/2010'," +
                "2000.67,'success','active'," +
                "'usr','','','',''," +
                "'pw','','','','')");
        
        stat.execute("INSERT INTO Jobs VALUES ('UID002','BR001','06/09/2010','05/30/2010'," +
                "2000.67,'success','active'," +
                "'usr','','','',''," +
                "'pw','','','','')");
        stat.execute("INSERT INTO Jobs VALUES ('UID002','BR002','06/09/2010','05/30/2010'," +
                "2000.67,'success','active'," +
                "'usr','2 Jan Smuts Drive','','',''," +
                "'pw','','','','')");
        
        stat.execute("INSERT INTO Jobs VALUES ('UID003','BR001','06/09/2010','05/30/2010'," +
                "2000.67,'success','active'," +
                "'usr','','','',''," +
                "'pw','','','','')");
        stat.execute("INSERT INTO Jobs VALUES ('UID003','BR002','06/09/2010','05/30/2010'," +
                "2000.67,'success','inactive'," +
                "'usr','77 Jan Smuts Drive','','',''," +
                "'pw','','','','')");
        stat.execute("INSERT INTO Jobs VALUES ('UID003','BR003','06/09/2010','05/30/2010'," +
                "2000.67,'success','active'," +
                "'usr','9998765','','',''," +
                "'pw','1234','','','')");
        
        /* Common
         * 
         * INSERT INTO Common VALUES ('UID003', 'BR003',
                'acc190823', 'Peter Parker', '05/30/2010', 'no100922',
                'May', 2000.67, '08/30/2010',
                10000.67, 2000.67, 8000,
                1000, 1000, 2000, 250)
         */
        
        /* Telco
         * INSERT INTO Telco VALUES ('UID003', 'BR001',
                '011 456 8870', '13:45:56',
                55.55, 400.55, 88)
         */
        
        /* muncipality
         * INSERT INTO Municipality VALUES('UID003', 'BR002',
                '700KWh', 4002.88,
                '3456L', 30982.77,
                5523.00, 8832.77)
         */
        
        /*credit card
         * INSERT INTO CreditCards VALUES('UID003', 'BR003',
                'Visa', '22.7%',
                50000.00, 18000.70, 2000.00)
         */
        
    }
    
    public static void queryTables() throws SQLException
    {
        Statement stat = _Connection.createStatement();
        
        ResultSet result = stat.executeQuery("SELECT * FROM Test");
        result.next();
        System.out.println(result.getString("Name"));
    }
    
    
    public static void dropAndClose() throws SQLException
    {
        Statement stat = _Connection.createStatement();
        
        stat.execute("DROP TABLE Test");
        
        _Connection.close();
    }
}
